package com.resume.aipeople.repository;

import com.resume.aipeople.entity.VoiceCache;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 语音缓存数据访问层
 */
@Repository
public interface VoiceCacheRepository extends JpaRepository<VoiceCache, Long> {
    
    /**
     * 根据文本哈希值查找语音缓存
     */
    Optional<VoiceCache> findByTextHash(String textHash);
    
    /**
     * 根据文本内容和角色名称查找语音缓存
     */
    @Query("SELECT v FROM VoiceCache v WHERE v.originalText = :text AND v.characterName = :characterName")
    Optional<VoiceCache> findByTextAndCharacter(@Param("text") String text, @Param("characterName") String characterName);
    
    /**
     * 根据角色名称查找语音缓存
     */
    List<VoiceCache> findByCharacterName(String characterName);
    
    /**
     * 查找最常访问的语音缓存
     */
    @Query("SELECT v FROM VoiceCache v ORDER BY v.accessCount DESC")
    List<VoiceCache> findMostAccessedVoices();
    
    /**
     * 查找最近访问的语音缓存
     */
    @Query("SELECT v FROM VoiceCache v ORDER BY v.lastAccessedAt DESC")
    List<VoiceCache> findRecentlyAccessedVoices();
    
    /**
     * 查找过期的语音缓存（超过指定天数未访问）
     */
    @Query("SELECT v FROM VoiceCache v WHERE v.lastAccessedAt < :expiryDate")
    List<VoiceCache> findExpiredVoices(@Param("expiryDate") LocalDateTime expiryDate);
    
    /**
     * 更新访问次数和最后访问时间
     */
    @Modifying
    @Query("UPDATE VoiceCache v SET v.accessCount = v.accessCount + 1, v.lastAccessedAt = :now WHERE v.id = :id")
    void updateAccessInfo(@Param("id") Long id, @Param("now") LocalDateTime now);
    
    /**
     * 删除过期的语音缓存
     */
    @Modifying
    @Query("DELETE FROM VoiceCache v WHERE v.lastAccessedAt < :expiryDate")
    void deleteExpiredVoices(@Param("expiryDate") LocalDateTime expiryDate);
    
    /**
     * 统计语音缓存总数
     */
    @Query("SELECT COUNT(v) FROM VoiceCache v")
    long countTotalVoices();
    
    /**
     * 统计指定角色的语音缓存数量
     */
    long countByCharacterName(String characterName);
    
    /**
     * 统计语音缓存的总大小
     */
    @Query("SELECT SUM(v.audioSize) FROM VoiceCache v")
    Long getTotalCacheSize();
}
